<?php
/*********************************************************************************
 * Copyright (C) 2011-2013 X2Engine Inc. All Rights Reserved.
 *
 * X2Engine Inc.
 * P.O. Box 66752
 * Scotts Valley, California 95067 USA
 *
 * Company website: http://www.x2engine.com
 * Community and support website: http://www.x2community.com
 *
 * X2Engine Inc. grants you a perpetual, non-exclusive, non-transferable license
 * to install and use this Software for your internal business purposes.
 * You shall not modify, distribute, license or sublicense the Software.
 * Title, ownership, and all intellectual property rights in the Software belong
 * exclusively to X2Engine.
 *
 * THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT WARRANTIES OF ANY KIND, EITHER
 * EXPRESS OR IMPLIED, INCLUDING WITHOUT LIMITATION THE IMPLIED WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE, AND NON-INFRINGEMENT.
 ********************************************************************************/

/* @edition:pro */

/**
 * @package X2CRM.modules.reports.controllers
 */
class ReportsController extends x2base {

    public $modelClass="Reports";
    /**
     * Redirects to the first report.
     *
     * This method only exists for compatibility with the rest of the software.
     */
    public function actionIndex() {
        $this->redirect('gridReport');
    }


    public function actionGetOptions() {
        if (isset($_GET['field'])) {
            echo "";
        } elseif (isset($_GET['fieldType'])) {
            $field = $_GET['fieldType'];
            $term = $_GET['term'] . "%";
            $options = Yii::app()->db->createCommand()
                    ->select($field)
                    ->from('x2_contacts')
                    ->group($field)
                    ->where($field . ' LIKE :term', array('term' => $term))
                    ->queryAll();
            $data = array();
            foreach ($options as $item) {
                if (empty($item[$field])) {

                } else {
                    $data[] = $item[$field];
                }
            }
            echo CJSON::encode($data);
        }
    }

    public function getDateRange() {

        $dateRange = array();
        $dateRange['strict'] = false;
        if (isset($_GET['strict']) && $_GET['strict'])
            $dateRange['strict'] = true;

        $dateRange['range'] = 'custom';
        if (isset($_GET['range']))
            $dateRange['range'] = $_GET['range'];

        switch ($dateRange['range']) {

            case 'thisWeek':
                $dateRange['start'] = strtotime('mon this week'); // first of this month
                $dateRange['end'] = time(); // now
                break;
            case 'thisMonth':
                $dateRange['start'] = mktime(0, 0, 0, date('n'), 1); // first of this month
                $dateRange['end'] = time(); // now
                break;
            case 'lastWeek':
                $dateRange['start'] = strtotime('mon last week'); // first of last month
                $dateRange['end'] = strtotime('mon this week') - 1;  // first of this month
                break;
            case 'lastMonth':
                $dateRange['start'] = mktime(0, 0, 0, date('n') - 1, 1); // first of last month
                $dateRange['end'] = mktime(0, 0, 0, date('n'), 1) - 1;  // first of this month
                break;
            case 'thisYear':
                $dateRange['start'] = mktime(0, 0, 0, 1, 1);  // first of the year
                $dateRange['end'] = time(); // now
                break;
            case 'lastYear':
                $dateRange['start'] = mktime(0, 0, 0, 1, 1, date('Y') - 1);  // first of last year
                $dateRange['end'] = mktime(0, 0, 0, 1, 1, date('Y')) - 1;   // first of this year
                break;
            case 'all':
                $dateRange['start'] = 0;        // every record
                $dateRange['end'] = time();
                if (isset($_GET['end'])) {
                    $dateRange['end'] = Formatter::parseDate($_GET['end']);
                    if ($dateRange['end'] == false)
                        $dateRange['end'] = time();
                    else
                        $dateRange['end'] = strtotime('23:59:59', $dateRange['end']);
                }
                break;

            case 'custom':
            default:
                $dateRange['end'] = time();
                if (isset($_GET['end'])) {
                    $dateRange['end'] = Formatter::parseDate($_GET['end']);
                    if ($dateRange['end'] == false)
                        $dateRange['end'] = time();
                    else
                        $dateRange['end'] = strtotime('23:59:59', $dateRange['end']);
                }

                $dateRange['start'] = strtotime('1 month ago', $dateRange['end']);
                if (isset($_GET['start'])) {
                    $dateRange['start'] = Formatter::parseDate($_GET['start']);
                    if ($dateRange['start'] == false)
                        $dateRange['start'] = strtotime('-30 days 0:00', $dateRange['end']);
                    else
                        $dateRange['start'] = strtotime('0:00', $dateRange['start']);
                }
        }
        return $dateRange;
    }

    /**
     * Lists all models.
     */
    public function actionLeadPerformance() {
        //print_r($_GET);exit;
        $fieldVar = isset($_GET['field']) ? $_GET['field'] : "leadSource";
        $dateRange = $this->getDateRange();
        $model = new Contacts('search');
        if (isset($_GET['Contacts']))
            $model->attributes = $_GET['Contacts'];
        $input = $model->$fieldVar;

        if (isset($_GET['Contacts']['company_id'], $_GET['Contacts']['company']) && !empty($_GET['Contacts']['company'])) { // check the ID, if provided
            $linkId = $_GET['Contacts']['company_id'];
            if (!empty($linkId) && X2Model::model('Accounts')->countByAttributes(array('id' => $linkId))) // if the link model actually exists,
                $model->company = $linkId;                 // then use the ID as the field value
        }
        if (!empty($_GET['Contacts']['company']) && !ctype_digit((string)$_GET['Contacts']['company'])) { // if the field is sitll text, try to find the ID based on the name
            $linkModel = X2Model::model('Accounts')->findByAttributes(array('name' => $_GET['Contacts']['company']));
            if (isset($linkModel))
                $model->company = $linkModel->id;
        }


        $attributeConditions = '';

        $attributeParams = array();
        // $attributeConditions = array();
        //$model->attributes
        foreach ($model->attributes as $key => $value) {
            if (!empty($value)) {
                $attributeConditions .= ' AND x2_contacts.' . $key . '=:' . $key;
                $attributeParams[':' . $key] = $value;
            }
        }

        if ($dateRange['strict'])
            $attributeConditions .= ' AND createDate BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end'];

        $workflow = 1;

        if (isset($_GET['workflow'])) {

            if (ctype_digit($_GET['workflow']))
                $workflow = $_GET['workflow'];


            $workflowStages = WorkflowStage::model()->findAllByAttributes(array('workflowId' => $workflow), array('order' => 'stageNumber ASC'));

            $stageIds = array();
            foreach ($workflowStages as $stage) {
                $stageIds[$stage->name] = $stage->stageNumber;
            }



            // SELECT COUNT(`a`.*) as `count1`, COUNT(`b`.*) as `count2`, assignedTo, stageNumber FROM `x2_actions` `a`, `x2_actions` `b` WHERE a.type="workflow" AND b.type="workflow" AND a.workflowId=1 AND b.workflowId=1 AND `a`.stageNumber=1 AND `b`.stageNumber=2 GROUP BY a.assignedTo, a.stageNumber, b.stageNumber


            $users = UserChild::getNames();

            $assignedTo = array_keys($users);

            $data = array();
            $totals = array(
                'id' => '',
                'name' => Yii::t('charts', 'Total'),
                'leads' => 0,
            );

            for ($i = 0, $size = sizeof($assignedTo); $i < $size; $i++) {

                $data[$i]['id'] = $assignedTo[$i];
                $data[$i]['name'] = $users[$assignedTo[$i]];


                if ($data[$i]['id'] == 'Anyone') {
                    $assignmentCheck = '(x2_contacts.assignedTo IS NULL OR x2_contacts.assignedTo="" OR x2_contacts.assignedTo="Anyone")';
                    $data[$i]['id'] = '';
                } else
                    $assignmentCheck = 'x2_contacts.assignedTo="' . $data[$i]['id'] . '"';


                $data[$i]['leads'] = Yii::app()->db->createCommand()
                        ->select('COUNT(*)')->from('x2_contacts')
                        ->where('assignedTo="' . $assignedTo[$i] . ($dateRange['strict'] ? '"' : '" AND createDate BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end']) . $attributeConditions, $attributeParams)
                        ->queryScalar();

                $totals['leads'] += $data[$i]['leads'];
                $str = "";
                foreach ($stageIds as $name => $id) {
                    $str.=" SUM(IF(x2_actions.stageNumber='$id',1,0)) AS `$name`, ";
                }
                $row = Yii::app()->db->createCommand()
                        ->select($str . ' x2_contacts.assignedTo AS assignedTo')
                        ->from('x2_contacts')
                        ->join('x2_actions', 'x2_actions.associationId=x2_contacts.id AND x2_actions.associationType="contacts"')
                        ->where('x2_actions.type="workflow" AND x2_actions.workflowId=' . $workflow . ' AND x2_actions.completeDate BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end'] . ' AND ' . $assignmentCheck
                                . ' AND (SELECT COUNT(*) FROM x2_contacts WHERE x2_contacts.id=x2_actions.associationId ' . $attributeConditions . ') > 0', $attributeParams)
                        ->queryRow();
                foreach ($stageIds as $name => $id) {
                    $data[$i][$name] = isset($row[$name]) ? $row[$name] : 0;
                    $totals[$name] = isset($totals[$name]) ? $totals[$name] + $data[$i][$name] : $data[$i][$name];
                }


                if (array_sum($data[$i]) == 0)
                    unset($data[$i]);
            }

            $data[] = $totals;

            // die(var_dump($data));
            // $sql = 'SELECT COUNT(*) as `count`,assignedTo, stageNumber FROM `x2_actions` WHERE type="workflow" AND workflowId=1 GROUP BY assignedTo, stageNumber';
            $dataProvider = new CArrayDataProvider($data, array(
                        // 'totalItemCount'=>$count,
                        // 'sort'=>'assignedTo ASC',
                        'sort' => array(
                        // 'defaultOrder'=>'name ASC',
                        // 'attributes'=>array(
                        // 'id', 'username', 'email',
                        // ),
                        ),
                        'pagination' => array(
                            'pageSize' => Yii::app()->params->profile->resultsPerPage,
                        ),
                    ));
        } else {
            $dataProvider = null;
            $stageIds = array();
        }
        $model->unsetFilters();
        $this->render('leadPerformance', array(
            'model' => $model,
            'workflow' => $workflow,
            'dataProvider' => $dataProvider,
            'dateRange' => $dateRange,
            'stageIds' => $stageIds,
            'fieldName' => $fieldVar,
            'input' => $input,
        ));
    }

    public function actionGridReport() {
        ini_set('memory_limit', -1);
        set_time_limit(0);
        $fieldVar1 = isset($_GET['field1']) ? $_GET['field1'] : "";
        $fieldVar2 = isset($_GET['field2']) ? $_GET['field2'] : "";
        $deleteList = array();
        $columnStructure = array();
        $columnList = array();
        $exportFlag = false;
        $dateRange = $this->getDateRange();

        // load fields from a particular model, Contacts by default
        if(isset($_GET['module'])) {
        	$module = $_GET['module'];
        } else {
        	$module = "Contacts";
        }
        if($module == "Contacts")
        	$model = new Contacts('search');
        else if($module == 'Services')
        	$model = new Services('search');
        else if ($module == 'Accounts')
        	$model = new Accounts('search');
        else if($module == 'Opportunity')
        	$model = new Opportunity('search');

        if (isset($_GET['Contacts']))
            $model->attributes = $_GET['Contacts'];
        if (isset($_GET['Contacts']['company_id'], $_GET['Contacts']['company']) && !empty($_GET['Contacts']['company'])) { // check the ID, if provided
            $linkId = $_GET['Contacts']['company_id'];
            if (!empty($linkId) && X2Model::model('Accounts')->countByAttributes(array('id' => $linkId))) // if the link model actually exists,
                $model->company = $linkId;                 // then use the ID as the field value
        }
        if (!empty($_GET['Contacts']['company']) && !ctype_digit($_GET['Contacts']['company'])) { // if the field is sitll text, try to find the ID based on the name
            $linkModel = X2Model::model('Accounts')->findByAttributes(array('name' => $_GET['Contacts']['company']));
            if (isset($linkModel))
                $model->company = $linkModel->id;
        }
        $attributeConditions = '';
        $attributeParams = array();
        foreach ($model->attributes as $key => $value) {
            if (!empty($value)) {
                $attributeParams[':' . $key] = $value;
            }
        }
        $attributeConditions .= ' AND createDate BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end'];
        if (isset($_GET['field1']) && isset($_GET['field2'])) {
            $fieldVar1 = isset($_GET['field1']) ? $_GET['field1'] : "";
            $fieldVar2 = isset($_GET['field2']) ? $_GET['field2'] : "";
            $fieldInfo = Fields::model()->findByAttributes(array('modelName' => $module, 'fieldName' => $fieldVar1));
            $options = Yii::app()->db->createCommand()
                    ->select($fieldVar1)
                    ->from($model->tableName())
                    ->group($fieldVar1)
                    ->queryAll();
            $field1Data = array();
            foreach ($options as $item) {
                if (empty($item[$fieldVar1])) {

                } else {
                    $field1Data[] = $item[$fieldVar1];
                }
            }
            $options2 = Yii::app()->db->createCommand()
                    ->select($fieldVar2)
                    ->from($model->tableName())
                    ->group($fieldVar2)
                    ->queryAll();
            $field2Data = array();
            foreach ($options2 as $item) {
                if (empty($item[$fieldVar2])) {

                } else {
                    $field2Data[] = $item[$fieldVar2];
                }
            }
            $data = array();
            $totals = array(
                'id' => '',
                'name' => Yii::t('charts', 'Total'),
                'leads' => 0,
            );
            $structure = array();
            $rowStructure = array();
            $columnStructure = array();
            $rows = Yii::app()->db->createCommand()
                    ->select("$fieldVar1")
                    ->from($model->tableName())
                    ->group("$fieldVar1")
                    ->queryAll();
            foreach ($rows as $row) {
                switch ($fieldInfo->type) {
                    case "assignment":
                        $keyUser = User::model()->findByAttributes(array('username' => $row[$fieldVar1]));
                        if (isset($keyUser)) {
                            $row[$fieldVar1] = $keyUser->name;
                        }
                        break;
                    case "date":
                        $row[$fieldVar1] = empty($row[$fieldVar1]) ? ' ' : Formatter::formatDate($row[$fieldVar1],'medium');
                        break;
                    case "link":
                        if (is_numeric($row[$fieldVar1]) && $row[$fieldVar1] > 0) {
                            $modelRecord = X2Model::model(ucfirst($fieldInfo->linkType))->findByPk($row[$fieldVar1]);
                            if (isset($modelRecord))
                                $row[$fieldVar1] = $modelRecord->name;
                        }
                        break;
                    case "text":
                        $row[$fieldVar1] = Formatter::truncateText($row[$fieldVar1]);
                        break;
                    case "boolean":
                        $row[$fieldVar1] = $row[$fieldVar1] ? "Yes" : "No";
                        break;
                    default:
                        break;
                }
                if (!in_array($row[$fieldVar1], $rowStructure)) {
                    $rowStructure[] = $row[$fieldVar1];
                }
            }
            $columns = Yii::app()->db->createCommand()
                    ->select("$fieldVar2")
                    ->from($model->tableName())
                    ->group("$fieldVar2")
                    ->queryAll();
            $fieldInfo2 = Fields::model()->findByAttributes(array('modelName' => $module, 'fieldName' => $fieldVar2));
            foreach ($columns as $column) {
                $columnStructure[] = $column[$fieldVar2];
            }
            foreach ($rowStructure as $row) {
                foreach ($columnStructure as $column) {
                    $structure[$row][$column] = 0;
                }
            }
            $selection = "";
            $selectionKey = "COUNT(*)";
            if (isset($_GET['cellType']) && isset($_GET['cellData'])) {
                $cellField = $_GET['cellData'];
                if ($_GET['cellType'] == 'sum') {
                    $selection = "SUM(" . $_GET['cellData'] . ")";
                    $selectionKey = $selection;
                } elseif ($_GET['cellType'] == 'avg') {
                    $selection = "AVG(" . $_GET['cellData'] . ")";
                    $selectionKey = $selection;
                }else {
                    $selection = "COUNT(*)";
                    $selectionKey = $selection;
                }
            } else {
                $selection = "COUNT(*)";
                $selectionKey = $selection;
            }
            if ($fieldInfo->type == 'date') {
                $dateField=$fieldVar1;
            } else {
                $dateField="createDate";
            }
            $sql = "$fieldVar1, $fieldVar2, $selection";
            $rowTest = Yii::app()->db->createCommand()
                    ->select($sql)
                    ->from($model->tableName())
                    ->group("$fieldVar1, $fieldVar2")
                    ->where($dateField.' BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end'])
                    ->queryAll();
            //printR($rowTest,true);
            foreach ($rowTest as $id => $array) {
                switch ($fieldInfo->type) {
                    case "assignment":
                        $keyUser = User::model()->findByAttributes(array('username' => $array[$fieldVar1]));
                        if (isset($keyUser)) {
                            $array[$fieldVar1] = $keyUser->name;
                        }
                        break;
                    case "date":
                        $array[$fieldVar1] = empty($array[$fieldVar1]) ? ' ' : Formatter::formatDate($array[$fieldVar1],'medium');
                        break;
                    case "link":
                        if (is_numeric($array[$fieldVar1]) && $array[$fieldVar1] > 0) {
                            $modelRecord = X2Model::model(ucfirst($fieldInfo->linkType))->findByPk($array[$fieldVar1]);
                            if (isset($modelRecord))
                                $array[$fieldVar1] = $modelRecord->name;
                        }
                        break;
                    case "text":
                        $array[$fieldVar1] = Formatter::truncateText($array[$fieldVar1]);
                        break;
                    case "boolean":
                        $array[$fieldVar1] = $array[$fieldVar1] ? "Yes" : "No";
                        break;
                    default:
                        break;
                }
                $structure[$array[$fieldVar1]][$array[$fieldVar2]]=isset($structure[$array[$fieldVar1]][$array[$fieldVar2]])? $structure[$array[$fieldVar1]][$array[$fieldVar2]]+$array[$selectionKey] : $array[$selectionKey];
            }

            $i = 0;
            foreach ($structure as $key => $values) {
                $dataRow = array();
                $temp = array();
                $tempKey = $key;
                $dataRow['id'] = $i;

                if ($key == "")
                    $key = "(none)";
                $dataRow['name'] = $key;
                foreach ($values as $newKey => $newValue) {

                    if (is_numeric($newValue) && !empty($newValue)) {
                        $newValue = round($newValue, 2);
                    }

                    if(!empty($newKey)){
                        $dataRow[$newKey] = $newValue;
                    }else{
                        $dataRow['(none)'] = $newValue;
                    }
                    $temp[$newKey] = $newValue;
                    if (!isset($_GET['cellType']) || $_GET['cellType'] == 'sum' || empty($_GET['cellType'])) {
                        if (empty($newKey)) {
                            isset($totals['(none)']) ? $totals['(none)']+=$newValue : $totals['(none)'] = $newValue;
                        }else
                            isset($totals[$newKey]) ? $totals[$newKey]+=$newValue : $totals[$newKey] = $newValue;
                    } else {
                        $newValue = Yii::app()->db->createCommand()
                                ->select("AVG($cellField)")
                                ->from($model->tableName())
                                ->where($dateField.' BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end'] . " AND $fieldVar2=:$fieldVar2", array(":$fieldVar2" => $newKey))
                                ->queryRow();
                        $newValue = round($newValue["AVG($cellField)"], 2);
                        if (empty($newKey))
                            $totals['(none)'] = $newValue;
                        else
                            $totals[$newKey] = $newValue;
                    }
                }
                $i++;
                if ((isset($_GET['zero']) && $_GET['zero'] &&
                        (array_sum($temp) != 0 && $key != '(none)'))
                        || !isset($_GET['zero']) || !$_GET['zero']){
                    $dataRow['leads']=array_sum($temp);
                    $totals['leads'] += $dataRow['leads'];

                    $data[] = $dataRow;
                }
            }

            $totals['id'] = count($data);
            $data[] = $totals;
            foreach ($columns as $column) {
                $temp = $column[$fieldVar2];
                switch ($fieldInfo2->type) {
                    case "assignment":
                        $temp = User::model()->findByAttributes(array('username' => $temp));
                        if(isset($temp))
                            $temp=$temp->name;
                        break;
                    case "date":
                        $temp = empty($temp) ? ' ' : Formatter::formatDate($temp,'medium');
                        break;
                    case "link":
                        if (is_numeric($temp) && $temp > 0) {
                            $modelRecord = X2Model::model(ucfirst($fieldInfo2->linkType))->findByPk($temp);
                            if (isset($modelRecord))
                                $temp = $modelRecord->name;
                        }
                        break;
                    case "text":
                        $temp = Formatter::truncateText($temp);
                        break;
                    case "boolean":
                        $temp = $temp ? "Yes" : "No";
                        break;
                    default:
                        break;
                }
                if (empty($temp))
                    $temp = "(none)";
                if (!in_array($temp, $columnList) && $temp!="" && $temp!=" ")
                    $columnList[] = $temp;
                foreach ($data as $id => &$array) {
                    if (!empty($column[$fieldVar2]) && $temp != $column[$fieldVar2]) {
                        if(!isset($array[$temp]))
                            $array[$temp]=0;
                        $array[$temp] += isset($array[$column[$fieldVar2]])?$array[$column[$fieldVar2]]:0;
                        if (array_key_exists($column[$fieldVar2], $totals)){
                            if(!isset($totals[$temp]))
                                $totals[$temp]=0;
                            $totals[$temp] += isset($totals[$column[$fieldVar2]])?$totals[$column[$fieldVar2]]:0;
                        }
                        unset($totals[$column[$fieldVar2]]);
                        unset($array[$column[$fieldVar2]]);
                    }else {
                        if (isset($array[""])) {
                            $array[$temp] = $array[""];
                            unset($array[""]);
                        }
                        if (isset($totals[""])) {
                            $totals[$temp] = $totals[""];
                            unset($totals[""]);
                        }
                    }
                }
            }
            if (isset($_GET['zero']) && $_GET['zero']) {
                foreach ($totals as $key => $row) {
                    if (empty($row)) {
                        $deleteList[] = $key;
                    }
                }
            }

            $dataProvider = new CArrayDataProvider($data, array(
                        'pagination' => array(
                            'pageSize' => PHP_INT_MAX,
                        ),
                    ));
            $exportFlag = true;
            $file = "gridExport.csv";
            $fp = fopen($file, "w+");
            fputcsv($fp, array_keys($data[0]));
            foreach ($data as $row) {
                fputcsv($fp, $row);
            }

            fclose($fp);

            $dataProvider = new CArrayDataProvider($data, array(
                        'pagination' => array(
                            'pageSize' => PHP_INT_MAX,
                        ),
                    ));

            } else {
                $dataProvider = null;
                $field2Data = array();
            }

        $report = null;
        if(isset($_GET['id'])) {
        	$report = Reports::model()->findByPk($_GET['id']);
        }

        $model->unsetFilters();
        $this->render('gridReport', array(
        		'module'=> $module,
                'model' => $model,
                'dataProvider' => $dataProvider,
                'dateRange' => $dateRange,
                'columnData' => array_diff($columnList, $deleteList),
                'field1' => $fieldVar1,
                'field2' => $fieldVar2,
                'cellType' => isset($_GET['cellType']) ? $_GET['cellType'] : "",
                'cellData' => isset($_GET['cellData']) ? $_GET['cellData'] : "",
                'zero' => ((isset($_GET['zero']) && $_GET['zero']) || !isset($_GET['field1'])),
                'exportFlag' => $exportFlag,
                'report'=>$report,
            ));

    }

    public function actionPrintReport() {
    	$table = $_POST['ReportTable'];
    	$images = array();
    	if(isset($_POST['ChartImage'])) {
    		foreach($_POST['ChartImage'] as $imageData) {
    			$images[] = $imageData;
    		}
    	}

    	$this->renderPartial('printReport', array(
    		'table'=>$table,
    		'images'=>$images,
    	), false, true);
    }


    public function actionDealReport() {

		$dataProvider = null;

		$dateRange = $this->getDateRange();
        if(isset($_GET['model']) && $_GET['model']=='opportunity'){
            $model = new Opportunity('search');
            $modelName="Opportunity";
        }elseif(isset($_GET['model']) && $_GET['model']=='contacts'){
            $model = new Contacts('search');
            $modelName="Contacts";

            // $input=$model->$fieldVar;
        }elseif(isset($_GET['model']) && $_GET['model']=='accounts'){
            $model=new Accounts('search');
            $modelName='Accounts';
        }else{
            $model = new Contacts('search');
            $modelName="Contacts";
        }

        if($model->hasAttribute('company'))
                    $companyField='company';
        elseif($model->hasAttribute('accountName'))
            $companyField='accountName';

        if(isset($_GET[$modelName]))
                $model->attributes = $_GET[$modelName];
        if(isset($companyField)){
            if(isset($_GET[$modelName]['company_id'],$_GET[$modelName][$companyField]) && !empty($_GET[$modelName][$companyField])) {	// check the ID, if provided
                $linkId = $_GET[$modelName]['company_id'];
                if(!empty($linkId) && X2Model::model('Accounts')->countByAttributes(array('id'=>$linkId)))	// if the link model actually exists,
                    $model->$companyField = $linkId;																	// then use the ID as the field value
            }
            if(!empty($_GET[$modelName][$companyField]) && !ctype_digit($_GET[$modelName][$companyField])) {	// if the field is sitll text, try to find the ID based on the name
                $linkModel = X2Model::model('Accounts')->findByAttributes(array('name'=>$_GET[$modelName][$companyField]));
                if(isset($linkModel))
                    $model->$companyField = $linkModel->id;
            }
        }


		$attributeConditions = '';

		$attributeParams = array(
			':date1'=> $dateRange['start'],
			':date2'=> $dateRange['end'],
		);
        if($model->hasAttribute('closedate')){
            $attributeConditions .= ' closedate BETWEEN :date1 AND :date2';
        }elseif($model->hasAttribute('expectedCloseDate')){
            $attributeConditions .= ' expectedCloseDate BETWEEN :date1 AND :date2';
        }elseif($model->hasAttribute('createDate')){
            $attributeConditions .= ' createDate BETWEEN :date1 AND :date2';
        }
		foreach($model->attributes as $key=>$value) {
			if(!empty($value)) {
				$attributeConditions .= ' AND '.$key.'=:'.$key;
				$attributeParams[':'.$key] = $value;
			}
		}
		if ($dateRange['strict'])
            $attributeConditions .= ' AND createDate BETWEEN ' . $dateRange['start'] . ' AND ' . $dateRange['end'];


        $criteria = new CDbCriteria(array(
            'condition'=>$attributeConditions,
            'params'=>$attributeParams,
            'distinct'=>true,
        ));
        if($model->hasAttribute('dealvalue')){
            $valueField="dealvalue";
        }elseif($model->hasAttribute('quoteAmount')){
            $valueField='quoteAmount';
        }
        if($model->hasAttribute('rating')){
            $probability='((rating*20)/100)';
        }elseif($model->hasAttribute('probability')){
            $probability='probability/100';
        }
        $valueString="";
        if(isset($valueField)){
            $valueString.=", SUM($valueField), SUM($valueField*$probability)";
        }
        $totalRecords=Yii::app()->db->createCommand()
                ->select("COUNT(*)".$valueString)
                ->from($model->tableName())
                ->where($attributeConditions,$attributeParams)
                ->queryRow();
        if($model->hasAttribute('dealstatus')){
            $status='dealstatus';
        }elseif($model->hasAttribute('salesStage')){
            $status='salesStage';
        }
        if(isset($valueField)){
            $currentValue=Yii::app()->db->createCommand()
                    ->select('SUM('.$valueField.')')
                    ->from($model->tableName())
                    ->where($attributeConditions.' AND '.$status.'="Won"',$attributeParams)
                    ->queryRow();
        }
        $total=$totalRecords['COUNT(*)'];
        if(isset($valueField)){
            $totalValue=$totalRecords["SUM($valueField)"];
            $projectedValue=$totalRecords["SUM($valueField*$probability)"];
            $currentAmount=$currentValue["SUM($valueField)"];
        }
        //printR($attributeConditions,true);

        // $dataProvider = new CArrayDataProvider($data,array(
        $dataProvider = new CActiveDataProvider($modelName,array(
            'criteria'=>$criteria,

            // 'totalItemCount'=>$count,
            // 'sort'=>'assignedTo ASC',
            'sort'=>array(
                // 'defaultOrder'=>'name ASC',
                // 'attributes'=>array(
                        // 'id', 'username', 'email',
                // ),
            ),
            'pagination'=>array(
                'pageSize'=>Yii::app()->params->profile->resultsPerPage,
            ),
        ));
        $parameters=json_encode($_GET);
		$this->render('dealReport', array(
			'model'=>$model,
			'dataProvider'=>$dataProvider,
			'dateRange'=>$dateRange,
			'total'=>$total,
            'totalValue'=>isset($totalValue)?$totalValue:"",
            'projectedValue'=>isset($projectedValue)?$projectedValue:"",
            'currentValue'=>isset($currentAmount)?$currentAmount:"",
            'modelName'=>isset($_GET['model'])?$_GET['model']:"contacts",
            'parameters'=>$parameters,
		));
	}

    public function actionSaveReport(){
        $report=new Reports;
        $type=$_GET['type'];
        $start=$_GET['start'];
        $end=$_GET['end'];
        $range=$_GET['range'];

        $report->type=$type;
        if($type=='grid'){

            $field1=$_GET['field1'];
            $field2=$_GET['field2'];
            $cellType=$_GET['cellType'];
            $cellData=$_GET['cellData'];
            $zero=$_GET['zero'];

            $report->cellType=$cellType;
            $report->cellData=$cellData;
            $report->field1=$field1;
            $report->field2=$field2;
            $report->zero=$zero;

        	if(isset($_GET['chartValue']) && isset($_GET['chartType'])) {
        		$rowPie = array();
        		for($i=0; $i<sizeof($_GET['chartValue']); $i++) {
        			$rowPie[] = $_GET['chartValue'][$i];
        			$rowPie[] = $_GET['chartType'][$i];
        		}
        		$report->rowPie = json_encode($rowPie);
        	}
        }elseif($type=='deal'){
            $report->parameters=$_GET['parameters'];
        }
        $report->start=$start;
        $report->end=$end;
        $report->dateRange=$range;

        $report->createDate=time();
        $report->createdBy=Yii::app()->user->getName();

        if($report->save()){

        }
        $this->redirect('savedReports');

    }

    public function actionSavedReports(){
        $dataProvider=new CActiveDataProvider('Reports',array(
            'sort'=>array(
                'defaultOrder'=>'createDate DESC'
            )
        ));

        $this->render('savedReports',array(
            'dataProvider'=>$dataProvider,
        ));
    }

    public function actionSaveTempImage() {
    	if(isset($_POST['ImageBase64']) && isset($_POST['ImageName'])) {
    		$base64image = $_POST['ImageBase64']; // the image data
    		$name = $_POST['ImageName'] . '.jpg';
    		$name = str_replace(' ', '_', $name);
    		$base64image = str_replace('data:image/png;base64,', '', $base64image);
    		$base64image = str_replace(' ', '+', $base64image);
    		$image = imagecreatefromstring(base64_decode($base64image));
    		if($image != false) {
    			$temp = TempFile::createTempFile($name);
    			if($temp && imagejpeg($image, $temp->fullpath(), 90))
    				echo json_encode(array('status' => 'success', 'id' => $temp->id, 'name' => $name));
    			else
    				echo json_encode(array('status' => 'fail', 'message' => Yii::t('media', 'Failed to upload file.')));
    		} else
    			echo json_encode(array('status' => 'fail', 'message' => Yii::t('media', 'Failed to upload file.')));
    	}
    }

    public function actionDelete($id) {
		$model=Reports::model()->findByPk($id);
//		if(Yii::app()->request->isPostRequest) {
			$model->delete();
//		} else
//			throw new CHttpException(400,Yii::t('app','Invalid request. Please do not repeat this request again.'));
			// if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser

		if(!isset($_GET['ajax']))
			$this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('savedReports'));
	}

    public function actionAdmin() {
        $this->redirect('index');
    }

    public function actionActivityReport(){
        $dataProvider = null;
        $trueData=array();
        $dateRange = $this->getDateRange();
		if(isset($_GET['range'])){

            $attributeConditions = '';

            $attributeParams = array(
                ':date1'=> $dateRange['start'],
                ':date2'=> $dateRange['end'],

            );
            $users=User::getNames();
            $data=array();
            foreach($users as $user=>$name){
                $data[$user]=array(
                    'id'=>array_search($user,array_keys($users)),
                    'username'=>$user,
                    'fullName'=>$name,
                );
            }
            $dueActionsData=Yii::app()->db->createCommand()
                    ->select('assignedTo, COUNT(*)')
                    ->from('x2_actions')
                    ->group('assignedTo')
                    ->where('dueDate BETWEEN :date1 AND :date2',$attributeParams)
                    ->queryAll();
            $completeActionsData=Yii::app()->db->createCommand()
                    ->select('assignedTo, COUNT(*)')
                    ->from('x2_actions')
                    ->group('assignedTo')
                    ->where('completeDate BETWEEN :date1 AND :date2',$attributeParams)
                    ->queryAll();
            $recordsUpdated=Yii::app()->db->createCommand()
                    ->select('changedBy, COUNT(*)')
                    ->from('x2_changelog')
                    ->group('changedBy')
                    ->where('timestamp BETWEEN :date1 AND :date2',$attributeParams)
                    ->queryAll();
            $login=Yii::app()->db->createCommand()
                    ->select('username, login')
                    ->from('x2_users')
                    ->where('status!=0')
                    ->group('username')
                    ->queryAll();
            foreach($login as $index=>$arr){
                $data[$arr['username']]['login']=$arr['login'];
            }
            foreach($data as $user=>&$array){
                if(!isset($array['login']))
                    $array['login']=0;
            }
            foreach($recordsUpdated as $index=>$arr){
                $data[$arr['changedBy']]['records']=$arr['COUNT(*)'];
            }
            foreach($data as $user=>&$array){
                if(!isset($array['records']))
                    $array['records']=0;
            }
            foreach($completeActionsData as $index=>$arr){
                $data[$arr['assignedTo']]['completed']=$arr['COUNT(*)'];
            }
            foreach($data as $user=>&$array){
                if(!isset($array['completed']))
                    $array['completed']=0;
            }
            foreach($dueActionsData as $index=>$arr){
                $data[$arr['assignedTo']]['due']=$arr['COUNT(*)'];
            }
            foreach($data as $user=>&$array){
                if(!isset($array['due']))
                    $array['due']=0;
            }
            unset($data['api']);
            $trueData=array();
            foreach($data as $key=>$arr){
                $userRecord=User::model()->findByAttributes(array('username'=>$key));
                if(isset($userRecord) && isset($arr['fullName'])){
                    $trueData[]=$arr;
                }
            }
        }

        $dataProvider = new CArrayDataProvider($trueData,array(
            'id'=>'activity-dataprovider',
            'pagination'=>array(
                'pageSize'=>ProfileChild::getResultsPerPage(),
            ),
            'sort'=>array(
                'attributes'=>array(
                    'fullName',
                ),
            ),
        ));
		$this->render('activityReport', array(
			'dataProvider'=>$dataProvider,
			'dateRange'=>$dateRange,
		));
    }

	public function actionWorkflow() {

		$dataProvider = null;


		$dateRange = $this->getDateRange();
		$model = new Contacts('search');
		if(isset($_GET['Contacts']))
			$model->attributes = $_GET['Contacts'];
		// $input=$model->$fieldVar;

		if(isset($_GET['Contacts']['company_id'],$_GET['Contacts']['company']) && !empty($_GET['Contacts']['company'])) {	// check the ID, if provided
			$linkId = $_GET['Contacts']['company_id'];
			if(!empty($linkId) && X2Model::model('Accounts')->countByAttributes(array('id'=>$linkId)))	// if the link model actually exists,
				$model->company = $linkId;																	// then use the ID as the field value
		}
		if(!empty($_GET['Contacts']['company']) && !ctype_digit($_GET['Contacts']['company'])) {	// if the field is sitll text, try to find the ID based on the name
			$linkModel = X2Model::model('Accounts')->findByAttributes(array('name'=>$_GET['Contacts']['company']));
			if(isset($linkModel))
				$model->company = $linkModel->id;
		}


		$attributeConditions = '';
        if($dateRange['strict'])
			$attributeConditions = 't.createDate BETWEEN :date1 AND :date2';
		$attributeParams = array(
			':date1'=> $dateRange['start'],
			':date2'=> $dateRange['end'],

		);
        if(Yii::app()->user->getName()!='admin'){
            $attributeParams[':user']=Yii::app()->user->name;
        }

		foreach($model->attributes as $key=>$value) {
			if(!empty($value)) {
				$attributeConditions .= ' AND x2_contacts.'.$key.'=:'.$key;
				$attributeParams[':'.$key] = $value;
			}
		}

		$workflow = null;
		$stage = '';
		if(isset($_GET['stage']) && ctype_digit($_GET['stage']))
			$stage = $_GET['stage'];
		if(isset($_GET['workflow']) && ctype_digit($_GET['workflow']))
			$workflow = $_GET['workflow'];

		if(isset($workflow,$stage)) {

			$attributeParams[':workflowId'] = $workflow;
            if(Yii::app()->user->getName()!='admin'){
            $condition = 't.visibility="1" OR t.assignedTo="Anyone"  OR t.assignedTo=:user';
            /* x2temp */
            $groupLinks = Yii::app()->db->createCommand()->select('groupId')->from('x2_group_to_user')->where('userId='.Yii::app()->user->getId())->queryColumn();
            if(!empty($groupLinks))
                $condition .= ' OR t.assignedTo IN ('.implode(',',$groupLinks).')';

            $condition .= 'OR (t.visibility=2 AND t.assignedTo IN
                (SELECT username FROM x2_group_to_user WHERE groupId IN
                    (SELECT groupId FROM x2_group_to_user WHERE userId='.Yii::app()->user->getId().')))';
            }else{
                $condition="";
            }
			$attributeConditions = $condition.$attributeConditions;

			$join = 'JOIN x2_actions ON x2_actions.associationId=t.id
				AND x2_actions.associationType="contacts"
				AND x2_actions.type="workflow"
				AND x2_actions.workflowId=:workflowId
				AND x2_actions.complete="Yes"
				AND x2_actions.completeDate BETWEEN :date1 AND :date2';

			if(!empty($stage)) {
				$join .= ' AND x2_actions.stageNumber=:stage';
				$attributeParams[':stage'] = $stage;
			}
			$criteria = new CDbCriteria(array(
				'condition'=>$attributeConditions,
				'join'=>$join,
				'params'=>$attributeParams,
				'distinct'=>true,
			));


			// $dataProvider = new CArrayDataProvider($data,array(
			$dataProvider = new CActiveDataProvider('Contacts',array(
				'criteria'=>$criteria,

				// 'totalItemCount'=>$count,
				// 'sort'=>'assignedTo ASC',
				'sort'=>array(
					// 'defaultOrder'=>'name ASC',
					// 'attributes'=>array(
						 // 'id', 'username', 'email',
					// ),
				),
				'pagination'=>array(
					'pageSize'=>Yii::app()->params->profile->resultsPerPage,
				),
			));
		} else {
			// $dataProvider = null;
			// $stageIds=array();
		}
		$model->unsetFilters();

		$workflowOptions = array();
		$stageOptions = array(''=>Yii::t('workflow','Any stage'));

		$query = Yii::app()->db->createCommand()
		->select('id,name')
		->from('x2_workflows')->query();
		while(($row = $query->read()) !== false)
			$workflowOptions[$row['id']] = $row['name'];

		// use the first workflow if none was selected
		if(!isset($workflow) && count($workflowOptions)) {
			reset($workflowOptions);
			$workflow = key($workflowOptions);
		}

		if(isset($workflow)) {
			$query = Yii::app()->db->createCommand()
				->select('stageNumber,name')
				->from('x2_workflow_stages')
				->where('workflowId=:id',array(':id'=>$workflow))
				->order('stageNumber ASC')
				->queryAll();

			for($i=0; $i<$size=count($query); $i++)
				$stageOptions[$query[$i]['stageNumber']] = $query[$i]['name'];
		}


		$this->render('workflow', array(
			'model'=>$model,
			'workflow'=>$workflow,
			'stage'=>$stage,
			'stageOptions'=>$stageOptions,
			'workflowOptions'=>$workflowOptions,
			'dataProvider'=>$dataProvider,
			'dateRange'=>$dateRange,
			// 'stageIds'=>$stageIds,
			// 'fieldName'=>$fieldVar,
			// 'input'=>$input,
		));
	}


}
